﻿using System;
using System.Data;
using System.Data.OracleClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

public partial class Contract_CellContract : UserPage
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Attribute_Bind();
            Dep_Bind();
            Auth();
            SetPager();
            RenewInit();
        }
    }

    protected void BtnCreate_Click(object sender, EventArgs e)
    {
        if (!IsOnlyContractNo())
        {
            JQFunction("$('#ContractDiv').dialog('open');$('#MsgDiv').show();$('#JQmsg').html('合同编号已存在！');$('#Contract_No').focus();$('body,html').animate({scrollTop:0},1000); ");
            return;
        }
        if (NotExistsCell())
        {
            JQFunction("$('#ContractDiv').dialog('open');$('#MsgDiv').show();$('#JQmsg').html('基站名称不存在！如果是新增基站，请先联系基站信息管理员！');$('#Cell_CHName').focus();$('body,html').animate({scrollTop:0},1000); ");
            return;
        }
        Renew();
        string sqlStr = @"INSERT INTO U_Cell_Contract_T(Contract_No,Cell_CHName,Cell_Type,Other_Name,Begin_Date
            ,End_Date,Pay_Date,Contract_Value,Pay_Mode,Contact_Info,Account_Name,Open_Bank,Account_No,Remark
            ,Status,Last_Operator,Last_OpeDate) VALUES(:Contract_No,:Cell_CHName,:Cell_Type,:Other_Name,:Begin_Date
                ,:End_Date,:Pay_Date,:Contract_Value,:Pay_Mode,:Contact_Info,:Account_Name,:Open_Bank,:Account_No
            ,:Remark,:Status,:Last_Operator,SYSDATE)";
        OracleParameter[] _params = {
            new OracleParameter(":Contract_No",OracleType.VarChar,64),
            new OracleParameter(":Cell_CHName",OracleType.NVarChar,50),
            new OracleParameter(":Cell_Type",OracleType.VarChar,20),
            new OracleParameter(":Other_Name",OracleType.NVarChar,32),
            new OracleParameter(":Begin_Date",OracleType.DateTime),
            new OracleParameter(":End_Date",OracleType.DateTime),
            new OracleParameter(":Pay_Date",OracleType.DateTime),
            new OracleParameter(":Contract_Value",OracleType.Number,16),
            new OracleParameter(":Pay_Mode",OracleType.VarChar,20),
            new OracleParameter(":Contact_Info",OracleType.VarChar,32),
            new OracleParameter(":Account_Name",OracleType.NVarChar,10),
            new OracleParameter(":Open_Bank",OracleType.NVarChar,32),
            new OracleParameter(":Account_No",OracleType.VarChar,32),
            new OracleParameter(":Remark",OracleType.NVarChar,128),
            new OracleParameter(":Status",OracleType.VarChar,20),
            new OracleParameter(":Last_Operator",OracleType.VarChar,16)
        };
        _params[0].Value = Contract_No.Text.Trim();
        _params[1].Value = Cell_CHName.Text.Trim();
        _params[2].Value = Cell_Type.SelectedValue;
        _params[3].Value = Other_Name.Text.Trim();
        _params[4].Value = Begin_Date.Text.Trim();
        _params[5].Value = End_Date.Text.Trim();
        _params[6].Value = Begin_Date.Text.Trim();
        _params[7].Value = Contract_Value.Text.Trim();
        _params[8].Value = Pay_Mode.SelectedValue;
        _params[9].Value = Contact_Info.Text.Trim();
        _params[10].Value = Account_Name.Text.Trim();
        _params[11].Value = Open_Bank.Text.Trim();
        _params[12].Value = Account_No.Text.Trim();
        _params[13].Value = Remark.Text.Trim();
        _params[14].Value = "正常";
        _params[15].Value = LoginUser;
        int res = OracleHelper.ExecuteNonQuery(sqlStr, CommandType.Text,_params);
        Renew();
        if (res == 1)
        {
            SetPager();
            ResetDiv();
            JQueryAlert("新增成功！");
        }
    }

    private void Renew()
    {
        if (C_No.Text.Length > 0)
        {
            string strSql = "UPDATE U_Cell_Contract_T SET Status = '已续签' WHERE Contract_No =:Contract_No";
            OracleParameter[] _params = {
                new OracleParameter(":Contract_No",OracleType.VarChar,64)
            };
            _params[0].Value = C_No.Text;
            OracleHelper.ExecuteNonQuery(strSql, CommandType.Text, _params);
        }
    }

    private void Dep_Bind()
    {
        string sqlstr = "SELECT DISTINCT SUBSTR(Replace(CELL_CHNAME,'G',''),0,2) AS County FROM CELL_CLASS ORDER BY SUBSTR(Replace(CELL_CHNAME,'G',''),0,2)";
        Dep_A.DataSource = OracleHelper.ExecuteDataSet(sqlstr, CommandType.Text, null).Tables[0].DefaultView;
        Dep_A.DataTextField = "County";
        Dep_A.DataValueField = "County";
        Dep_A.DataBind();
        Dep_A.Items.Insert(0, new ListItem("全市", "0"));
    }

    private void Auth()
    {
        DataRow dr = GetOrganAndCompany().Rows[0];
        if ((!dr["company"].ToString().Equals("延安")) || dr["organ"].ToString().Equals("宝塔维护站"))
        {
            Dep_A.SelectedValue = dr["company"].ToString();
        }
        else
        {
            Dep_A.Enabled = true;
        }
    }

    private bool IsOnlyContractNo()
    {
        string sqlStr = "SELECT count(1) FROM U_Cell_Contract_T WHERE Contract_No = :Contract_No AND Status <> '删除'";
        OracleParameter[] _params = {
                new OracleParameter(":Contract_No",OracleType.VarChar,64)
            };
        _params[0].Value = Contract_No.Text.Trim();
        return OracleHelper.ExecuteScalar(sqlStr, CommandType.Text, _params).ToString().Equals("0");
    }

    private bool NotExistsCell()
    {
        string sqlStr = "SELECT count(1) FROM CELL_CLASS WHERE Cell_CHName = :Cell_CHName ";
        if (Dep_A.SelectedValue != "0")
        {
            sqlStr += " AND Cell_CHName LIKE '" + Dep_A.SelectedValue + "%'";
        }
        OracleParameter[] _params = {
                new OracleParameter(":Cell_CHName",OracleType.NVarChar,50)
            };
        _params[0].Value = Cell_CHName.Text.Trim();
        return OracleHelper.ExecuteScalar(sqlStr, CommandType.Text, _params).ToString().Equals("0");
    }

    private DataSet GetContract()
    {
        string sqlStr = @"select Contract_No,Cell_Chname,Cell_Type,Other_Name,TO_CHAR(Begin_Date,'FMyyyy-mm-dd') AS Begin_Date
                   ,TO_CHAR(End_Date,'FMyyyy-mm-dd') AS End_Date,Contract_Value,TO_CHAR(Pay_Date,'FMyyyy-mm-dd') AS Pay_Date,Status
              from U_CELL_CONTRACT_T WHERE Status <> '删除' AND Cell_chname LIKE '%"
            + Tools.ReplaceString(Cell_CHName_A.Text.Trim(), 50) + "%'";
        if (Dep_A.SelectedValue != "0")
        {
            sqlStr += " AND Cell_chname LIKE '" + Dep_A.SelectedValue + "%'";
        }
        return OracleHelper.ExecuteDataSet(sqlStr, CommandType.Text, null);
    }

    private void SetPager()
    {
        PagedDataSource pds = new PagedDataSource();
        DataSet ds = GetContract();
        ContractPager.RecordCount = ds.Tables[0].Rows.Count;
        pds.DataSource = ds.Tables[0].DefaultView;
        pds.AllowPaging = true;
        pds.PageSize = ContractPager.PageSize;
        pds.CurrentPageIndex = ContractPager.CurrentPageIndex - 1;
        ContractList.DataSource = pds;
        ContractList.DataBind();
    }

    protected void QueryContract_Click(object sender, EventArgs e)
    {
        SetPager();
    }

    protected void Contract_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        if (e.CommandName == "Show")
        {
            ShowContract(e);
        }
        else if (e.CommandName == "ModifyContract")
        {
            ModifyContract(e);
        }
        else if (e.CommandName == "DeleteContract")
        {
            DeleteContract(e);
        }
    }

    private void DeleteContract(GridViewCommandEventArgs e)
    {
        string sqlStr = "UPDATE U_CELL_CONTRACT_T SET Status = '删除' WHERE Contract_No=:Contract_No";
        OracleParameter[] _params = {
                new OracleParameter(":Contract_No",OracleType.VarChar,64)
            };
        _params[0].Value = e.CommandArgument.ToString();
        int res = OracleHelper.ExecuteNonQuery(sqlStr, CommandType.Text, _params);
        if (res > 0)
        {
            SetPager();
            JQueryAlert("基站合同信息已删除！");
        }
    }

    private void ModifyContract(GridViewCommandEventArgs e)
    {
        Contract_Bind(e.CommandArgument.ToString());        
        BtnCreate.Visible = false;
        BtnModify.Visible = true;
        Contract_No.ReadOnly = true;
        Page.ClientScript.RegisterStartupScript(this.GetType(), "open"
            , "$(function() {$('#ContractDiv').dialog('open');})", true);
    }

    private void ShowContract(GridViewCommandEventArgs e)
    {
        Contract_Bind(e.CommandArgument.ToString());
        BtnModify.Visible = false;
        BtnCreate.Visible = true;
        BtnCreate.Enabled = false;
        Contract_No.ReadOnly = false;
        Page.ClientScript.RegisterStartupScript(this.GetType(), "open"
            , "$(function() {$('#ContractDiv').dialog('open');})", true);
    }

    private void Contract_Bind(string argNo)
    {
        string sqlStr = "SELECT * from U_CELL_CONTRACT_T WHERE Contract_No = :Contract_No";
        OracleParameter[] _params = {
            new OracleParameter(":Contract_No",OracleType.VarChar,64)
        };
        _params[0].Value = argNo;
        DataRow dr = OracleHelper.ExecuteDataSet(sqlStr, CommandType.Text, _params).Tables[0].Rows[0];
        Contract_No.Text = dr["Contract_No"].ToString();
        Cell_CHName.Text = dr["Cell_CHName"].ToString();
        Cell_Type.SelectedValue = dr["Cell_Type"].ToString();
        Other_Name.Text = dr["Other_Name"].ToString();
        Begin_Date.Text = dr["Begin_Date"].ToString();
        End_Date.Text = dr["End_Date"].ToString();
        Contract_Value.Text = dr["Contract_Value"].ToString();
        Pay_Mode.SelectedValue = dr["Pay_Mode"].ToString();
        Contact_Info.Text = dr["Contact_Info"].ToString();
        Account_Name.Text = dr["Account_Name"].ToString();
        Open_Bank.Text = dr["Open_Bank"].ToString();
        Account_No.Text = dr["Account_No"].ToString();
        Remark.Text = dr["Remark"].ToString();
    }

    protected void ContractPager_PageChanged(object sender, EventArgs e)
    {
        SetPager();
    }

    protected void AddContract_Click(object sender, EventArgs e)
    {
        ResetDiv();
        BtnCreate.Visible = true;
        BtnCreate.Enabled = true;
        BtnModify.Visible = false;
        Page.ClientScript.RegisterStartupScript(this.GetType(), "open"
            , "$(function() {$('#ContractDiv').dialog('open');})", true);
    }

    private void Attribute_Bind()
    {
        Begin_Date.Attributes.Add("onclick", "WdatePicker({el:$dp.$('Begin_Date')});");
        End_Date.Attributes.Add("onclick", "WdatePicker({el:$dp.$('End_Date')});");
    }

    private void ResetDiv()
    {
        Contract_No.Text = "";
        Cell_CHName.Text = "";
        Cell_Type.SelectedIndex = 0;
        Other_Name.Text = "";
        Begin_Date.Text = "";
        End_Date.Text = "";
        Contract_Value.Text = "";
        Pay_Mode.SelectedIndex = 0;
        Contact_Info.Text = "";
        Account_Name.Text = "";
        Open_Bank.Text = "";
        Account_No.Text = "";
        Remark.Text = "";
    }

    protected void BtnModify_Click(object sender, EventArgs e)
    {
        if (NotExistsCell())
        {
            JQFunction("$('#ContractDiv').dialog('open');$('#MsgDiv').show();$('#JQmsg').html('基站名称不存在！如果是新增基站，请先联系基站信息管理员！');$('#Cell_CHName').focus();$('body,html').animate({scrollTop:0},1000); ");
            return;
        }
        string sqlStr = @"UPDATE U_CELL_CONTRACT_T SET cell_chname = :cell_chname,cell_type=:cell_type
       ,other_name=:other_name,begin_date=:begin_date,end_date=:end_date
       ,contract_value=:contract_value,pay_mode=:pay_mode,contact_info=:contact_info
       ,account_name=:account_name,open_bank=:open_bank,account_no=:account_no
       ,remark=:remark,last_operator=:last_operator,last_opedate=sysdate
       WHERE contract_no = :contract_no";
        OracleParameter[] _params = {
            new OracleParameter(":contract_no",OracleType.VarChar,64),
            new OracleParameter(":cell_chname",OracleType.NVarChar,50),
            new OracleParameter(":cell_type",OracleType.VarChar,20),
            new OracleParameter(":other_name",OracleType.NVarChar,32),
            new OracleParameter(":begin_date",OracleType.DateTime),
            new OracleParameter(":end_date",OracleType.DateTime),
            new OracleParameter(":contract_value",OracleType.Number,16),
            new OracleParameter(":pay_mode",OracleType.VarChar,20),
            new OracleParameter(":contact_info",OracleType.VarChar,32),
            new OracleParameter(":account_name",OracleType.NVarChar,10),
            new OracleParameter(":open_bank",OracleType.NVarChar,32),
            new OracleParameter(":account_no",OracleType.VarChar,32),
            new OracleParameter(":remark",OracleType.NVarChar,128),
            new OracleParameter(":last_operator",OracleType.VarChar,16)
        };
        _params[0].Value = Contract_No.Text.Trim();
        _params[1].Value = Cell_CHName.Text.Trim();
        _params[2].Value = Cell_Type.SelectedValue;
        _params[3].Value = Other_Name.Text.Trim();
        _params[4].Value = Begin_Date.Text.Trim();
        _params[5].Value = End_Date.Text.Trim();
        _params[6].Value = Contract_Value.Text.Trim();
        _params[7].Value = Pay_Mode.SelectedValue;
        _params[8].Value = Contact_Info.Text.Trim();
        _params[9].Value = Account_Name.Text.Trim();
        _params[10].Value = Open_Bank.Text.Trim();
        _params[11].Value = Account_No.Text.Trim();
        _params[12].Value = Remark.Text.Trim();        
        _params[13].Value = LoginUser;
        int res = OracleHelper.ExecuteNonQuery(sqlStr, CommandType.Text, _params);
        if (res == 1)
        {
            SetPager();
            ResetDiv();
            JQueryAlert("基站合同信息更新成功！");
        }
    }

    private void RenewInit()
    {
        if (Request.QueryString["Renew"] != null)
        {
            C_No.Text = Request.QueryString["Renew"].ToString();
            Contract_Bind(C_No.Text);
            BtnCreate.Visible = true;
            BtnCreate.Enabled = true;
            BtnModify.Visible = false;
            Contract_No.ReadOnly = false;
            Page.ClientScript.RegisterStartupScript(this.GetType(), "open"
            , "$(function() {$('#ContractDiv').dialog('open');})", true);
        }
    }
}
